﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//using System.Data;
using System.IO;
using System.Xml;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using myrange = Microsoft.Office.Interop.Excel.Range;
using System.Xml.Serialization;
//System.Runtime.Serialization

namespace ExcelToXml
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_Change_Click(object sender, EventArgs e)
        {
            GetExcelData();
          //  StrToXml("12223 114 ");          
       }

       // List<System.Data.DataTable> list = new List<System.Data.DataTable>();
        List<string> list = new List<string>();
        public void GetExcelData()
        {
            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel Files|*.xls";

            if (ofd.ShowDialog() == DialogResult.OK)
            {
                string filename = ofd.FileName;

                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Workbook workbook;

                Microsoft.Office.Interop.Excel.Worksheet worksheet;

                object oMissing = System.Reflection.Missing.Value;//相当null

                workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                for (int k = 1; k <= 1; k++)
                {
                    worksheet = (Worksheet)workbook.Worksheets[k];
                    //行数
                    int rowCount = worksheet.UsedRange.Rows.Count;
                    //列数
                    int colCount = worksheet.UsedRange.Columns.Count;
                    //MessageBox.Show(rowCount.ToString());
                    //MessageBox.Show(colCount.ToString());

                    Microsoft.Office.Interop.Excel.Range range1;

                    // System.Data.DataTable dt = new System.Data.DataTable();

                    for (int i = 0; i < colCount; i++)
                    {
                        range1 = worksheet.get_Range((object)worksheet.Cells[1, i + 1], (object)worksheet.Cells[1, i + 1]);
                       // dt.Columns.Add(range1.Value2.ToString());
                    }

                    string str = "";
                    for (int j =5; j < 6; j++)
                    {                     
                        for (int i = 0; i < colCount; i++)
                        {                         
                            range1 = worksheet.get_Range((object)worksheet.Cells[j + 1, i + 1], (object)worksheet.Cells[j + 1, i + 1]);

                            if (!string.IsNullOrEmpty(range1.Value2.ToString()))
                            {
                                str= range1.Value2.ToString();
                                list.Add(str);
                             
                                //MessageBox.Show(str);                              
                              //  MessageBox.Show(range1.Value2.ToString());
                            }

                        }
                        MessageBox.Show(list.Count.ToString());  
                        if (j == 5)
                        {
                            StrToXml(list);
                            //MessageBox.Show(str);
                            break;
                        }                       
                    }                                        
                    excel.Quit();
                }
            }
        }
        public void StrToXml(List<string> list)
        {

            XmlDocument xmldoc = new XmlDocument();
            XmlDeclaration dec = xmldoc.CreateXmlDeclaration("1.0", "uft-8", null);
            xmldoc.AppendChild(dec);

            XmlElement root = xmldoc.CreateElement("c");
            root.SetAttribute("level", "file");
            xmldoc.AppendChild(root);
            XmlElement r1 = xmldoc.CreateElement("did");
            XmlElement rsub1 = xmldoc.CreateElement("unitid");
            rsub1.SetAttribute("identifier", " ");
            rsub1.InnerText = list[3] + list[4] + list[5];
            r1.AppendChild(rsub1);
            XmlElement rsub2 = xmldoc.CreateElement("unittitle");
            rsub2.SetAttribute("label", "簿冊標題");
            rsub2.InnerText = list[6];
            r1.AppendChild(rsub2);           
            root.AppendChild(r1);

            xmldoc.Save("D:\\3.xml");


            //  设置，添加节点

            //XmlDocument xmlDoc = new XmlDocument();
            //XmlDeclaration dec = xmlDoc.CreateXmlDeclaration("1.0", "GB2312", null);
            //xmlDoc.AppendChild(dec);
            ////创建一个根节点（一级）
            //XmlElement root = xmlDoc.CreateElement("First");
            //xmlDoc.AppendChild(root);

            //XmlElement xe1 = xmlDoc.CreateElement("book");//创建一个节点 
            //xe1.SetAttribute("genre", "李赞红");//设置该节点genre属性 
            //xe1.SetAttribute("ISBN", "2-3631-4");//设置该节点ISBN属性
            //XmlElement xesub1 = xmlDoc.CreateElement("title");
            //xesub1.InnerText = "CS从入门到精通";//设置文本节点 
            //xe1.AppendChild(xesub1);//添加到节点中 
            //XmlElement xesub2 = xmlDoc.CreateElement("author");
            //xesub2.InnerText = "候捷";
            //xe1.AppendChild(xesub2);
            //XmlElement xesub3 = xmlDoc.CreateElement("price");
            //xesub3.InnerText = "58.3";
            //xe1.AppendChild(xesub3);
            //root.AppendChild(xe1);//
            //xmlDoc.Save("D:\\bookstore.xml");

            MessageBox.Show("转化成功！");
        }
        private void button1_Click(object sender, EventArgs e)
        {
            //
            //string excelFilePath = "E:\\④別紙2　公文書データ項目表.xls";
            //    XmlDocument excelData = new XmlDocument();

            //    DataSet excelTableDataSet = new DataSet();
            //    StreamReader excelContent = new StreamReader(excelFilePath, System.Text.Encoding.Default);
            //    string stringConnectToExcelFile = string.Format("provider=Microsoft.ACE.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 8.0;");
            //    System.Data.OleDb.OleDbConnection oleConnectionToExcelFile = new System.Data.OleDb.OleDbConnection(stringConnectToExcelFile);
            //    System.Data.OleDb.OleDbDataAdapter oleDataAdapterForGetExcelTable = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [Sheet 1$]"), oleConnectionToExcelFile);
            //    //try
            //    //{
            //     //填充

            //    OleDbConnection con = newOleDbConnection(conn);
            //     oleDataAdapterForGetExcelTable.Fill(excelTableDataSet);
            //    //}
            //    //catch
            //    //{
            //    //    return null;
            //    //}
            //    //string excelOutputXml = Path.GetTempFileName();
            //excelTableDataSet.WriteXml(excelOutputXml);
            //excelData.Load(excelOutputXml);
            //File.Delete(excelOutputXml);
            //return excelData; 

        }
    }
}
           
   
